/****** Object:  Stored Procedure [dbo].spPhieuXuatInsert Script Date: 6/12/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spPhieuXuatInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spPhieuXuatInsert]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		19/06/2012	SongTM					Innit created
*******************************************************************************/

CREATE PROCEDURE [dbo].[spPhieuXuatInsert]
(
	@SoPX nvarchar(14) output,
	@NgayXuatKho datetime,
	@NguoiXuat nvarchar(35),
	@NgayLapPhieu datetime,
	@NguoiLap nvarchar(35),
	@SoHD nvarchar(12),
	@LyDoXuat nvarchar(200),
	@GhiChu nvarchar(300),
	@MaKH nvarchar(6),
	@MaKho nvarchar(5)
)
WITH ENCRYPTION

AS

SET NOCOUNT ON

	Declare @Stt char(3)
	Declare @y varchar(2)
	Declare @m varchar(2)

	set @y = RIGHT(YEAR(@NgayXuatKho),2)
	set @m =RIGHT(MONTH(@NgayXuatKho),2)
	if not exists (select * from [tbl_PhieuXuat])
		Set @Stt=1
	else if not exists (select * from tbl_PhieuXuat where RIGHT(YEAR(NgayXuatKho),2) = @y and RIGHT(MONTH(NgayXuatKho),2) = @m )
		Set @Stt=1
	else
		Set @Stt=(select MAX(RIGHT(LEFT(SoPX,5),3)) from tbl_PhieuXuat where RIGHT(YEAR(NgayXuatKho),2) = @y and RIGHT(MONTH(NgayXuatKho),2) = @m )+1
		
	Set @Stt=REPLICATE('0',3-LEN(@Stt))+@Stt
	set @m = REPLICATE('0',2-LEN(@m))+@m
	Set @SoPX='PXooo/mmyy/NP'
	Set @SoPX=REPLACE(@SoPX,'ooo',@Stt);
	Set @SoPX=REPLACE(@SoPX,'mm',@m);
	Set @SoPX=REPLACE(@SoPX,'yy',@y);

	INSERT INTO [tbl_PhieuXuat]
	(
		[SoPX],
		[NgayXuatKho],
		[NguoiXuat],
		[NgayLapPhieu],
		[NguoiLap],
		[SoHD],
		[LyDoXuat],
		[GhiChu],
		[MaKH],
		[MaKho]
	)
	VALUES
	(
		@SoPX,
		@NgayXuatKho,
		@NguoiXuat,
		@NgayLapPhieu,
		@NguoiLap,
		@SoHD,
		@LyDoXuat,
		@GhiChu,
		@MaKH,
		@MaKho
	)
	GO

	--GRANT EXECUTE ON [dbo].[spPhieuXuatInsert] TO [sa]
	--GO
